﻿using System;
using System.IO;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

public partial class Support_CellInfo : UserPage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SetPager();
            UnCellPager();
        }
    }

    private void SetPager()
    {
        PagedDataSource pds = new PagedDataSource();
        DataSet ds = GetCell();
        CellPager.RecordCount = ds.Tables[0].Rows.Count;
        pds.DataSource = ds.Tables[0].DefaultView;
        pds.AllowPaging = true;
        pds.PageSize = CellPager.PageSize;
        pds.CurrentPageIndex = CellPager.CurrentPageIndex - 1;
        CellInfo.DataSource = pds;
        CellInfo.DataBind();
    }

    private void UnCellPager()
    {
        PagedDataSource pds = new PagedDataSource();
        DataSet ds = GetUnCell();
        UnCellPage.RecordCount = ds.Tables[0].Rows.Count;
        pds.DataSource = ds.Tables[0].DefaultView;
        pds.AllowPaging = true;
        pds.PageSize = UnCellPage.PageSize;
        pds.CurrentPageIndex = UnCellPage.CurrentPageIndex - 1;
        UnCellList.DataSource = pds;
        UnCellList.DataBind();
    }

    private DataSet GetUnCell()
    {
        string sqlStr = @"select DISTINCT RNW_OBJECT_NAME AS Cell_Name,'3G' AS Net from ALARM_TABLE_3G 
                     WHERE CELL_CHNAME IS NULL
                    UNION ALL
                    SELECT DISTINCT ALARM_SOURCE_NAME AS Cell_Name,'2G' AS Net FROM ALARM_TABLE_2G A 
                     WHERE NOT EXISTS (SELECT 1 FROM CELL_CLASS B 
                               WHERE A.ALARM_SOURCE_NAME = B.CELL_ENNAME
                                OR A.ALARM_SOURCE_NAME = Replace(Replace(B.cell_chname,'---工程期',''),'-',''))
                       AND ALARM_SOURCE_NAME <> 'BSC'";
        return OracleHelper.ExecuteDataSet(sqlStr, CommandType.Text, null);
    }

    private DataSet GetCell()
    {
        string sqlstr = @"SELECT CELL_CHNAME,CELL_ENNAME,CELL_CLASS,CELL_CLASSIFICATION AS CELL_NET,CELL_NUMBER,CELL_IN
                            FROM CELL_CLASS ";
        if (CellInfo_I.Text.Trim().Length > 0)
        {
            sqlstr = sqlstr + " WHERE CELL_CHNAME LIKE '%" + Tools.ReplaceString(CellInfo_I.Text.Trim(),100) + "%'";
        }
        sqlstr = sqlstr + " ORDER BY CELL_CHNAME ";
        return OracleHelper.ExecuteDataSet(sqlstr, CommandType.Text, null);
    }

    protected void CellPager_PageChanged(object sender, EventArgs e)
    {
        SetPager();
    }

    protected void CellInfo_RowEditing(object sender, GridViewEditEventArgs e)
    {
        CellInfo.EditIndex = e.NewEditIndex;
        SetPager();
    }

    protected void CellInfo_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        CellInfo.EditIndex = -1;
        SetPager();
    }

    protected void CellInfo_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string sqlstr = "AB_CELLINFO_P";
        OracleParameter[] _params = {
            new OracleParameter("V_CELL",OracleType.NVarChar,100),
            new OracleParameter("v_CLASS",OracleType.NVarChar,20),
            new OracleParameter("v_NET",OracleType.NVarChar,20),
            new OracleParameter("V_NUMBER",OracleType.VarChar,10),
            new OracleParameter("V_ENNAME",OracleType.VarChar,100),
            new OracleParameter("V_CELLIN",OracleType.VarChar,2)
        };
        _params[0].Value = CellInfo.DataKeys[e.RowIndex].Value;
        DropDownList ddl1 = CellInfo.Rows[e.RowIndex].FindControl("CELL_CLASS_D") as DropDownList;
        DropDownList ddl2 = CellInfo.Rows[e.RowIndex].FindControl("CELL_NET_D") as DropDownList;
        TextBox tx = CellInfo.Rows[e.RowIndex].FindControl("CELL_NUMBER_T") as TextBox;
        TextBox tx2 = CellInfo.Rows[e.RowIndex].FindControl("CELL_ENNAME_T") as TextBox;
        DropDownList ddl3 = CellInfo.Rows[e.RowIndex].FindControl("CELL_IN_D") as DropDownList;
        _params[1].Value = ddl1.SelectedValue;
        _params[2].Value = ddl2.SelectedValue;
        _params[3].Value = tx.Text.Trim();
        _params[4].Value = tx2.Text.Trim();
        _params[5].Value = ddl3.SelectedValue;
        OracleHelper.ExecuteNonQuery(sqlstr, CommandType.StoredProcedure, _params);
        CellInfo.EditIndex = -1;
        ModifyINCell();
        SetPager();
        UnCellPager();        
    }

    protected void CellInfo_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowState == DataControlRowState.Edit)
        {
            if (e.Row.FindControl("CELL_CLASS_D") != null)
            {
                DropDownList ddl = (DropDownList)e.Row.FindControl("CELL_CLASS_D");
                ddl.SelectedValue = DataBinder.Eval(e.Row.DataItem, "CELL_CLASS").ToString();
            }
            if (e.Row.FindControl("CELL_NET_D") != null)
            {
                DropDownList ddl = (DropDownList)e.Row.FindControl("CELL_NET_D");
                ddl.SelectedValue = DataBinder.Eval(e.Row.DataItem, "CELL_NET").ToString();
            }
            if (e.Row.FindControl("CELL_IN_D") != null)
            {
                DropDownList ddl = (DropDownList)e.Row.FindControl("CELL_IN_D");
                ddl.SelectedValue = DataBinder.Eval(e.Row.DataItem, "CELL_IN").ToString();
            }
        }
    }

    protected void QueryUser_Click(object sender, EventArgs e)
    {
        SetPager();
    }

    protected void BtnCreate_Click(object sender, EventArgs e)
    {
        if (InvalidCell())
        {
            Page.ClientScript.RegisterStartupScript(this.GetType(), "sysinfo"
                , "$(function(){$('#AddCellDiv').dialog('open');alert('基站名称已存在');});", true);
            return;
        }
        string sqlstr = "AB_CELLINFO_P";
        OracleParameter[] _params = {
                new OracleParameter("V_CELL",OracleType.NVarChar,100),
                new OracleParameter("v_CLASS",OracleType.NVarChar,20),
                new OracleParameter("v_NET",OracleType.NVarChar,20),
                new OracleParameter("V_NUMBER",OracleType.VarChar,10),
                new OracleParameter("V_ENNAME",OracleType.VarChar,100),
                new OracleParameter("V_CELL_ADDRESS",OracleType.NVarChar,64)
            };
        _params[0].Value = CellInfo_A.Text.Trim();
        _params[1].Value = CellClass_A.SelectedValue;
        _params[2].Value = CellNet_A.SelectedValue;
        _params[3].Value = CellNumber_A.Text.Trim();
        _params[4].Value = CellEnInfo_A.Text.Trim();
        _params[5].Value = CELL_ADDRESS.Text.Trim();
        int count = OracleHelper.ExecuteNonQuery(sqlstr, CommandType.StoredProcedure, _params);
        SetPager();
        UnCellPager();
        if (count > 0)
        {
            JQueryAlert("基站新增成功！");
        }
    }

    private bool InvalidCell()
    {
        string sqlstr = "Select count(1) from cell_class where cell_chname = :cell_chnamme";
        OracleParameter[] _params = {
                new OracleParameter(":cell_chnamme",OracleType.NVarChar,100)
            };
        _params[0].Value = CellInfo_A.Text.Trim();
        int res = Int32.Parse(OracleHelper.ExecuteScalar(sqlstr, CommandType.Text, _params).ToString());
        return res > 0;
    }

    protected void CellInfo_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string sqlstr = "DELETE FROM CELL_CLASS WHERE CELL_CHNAME = :CELL_CHNAME";
        OracleParameter[] _params = {
                new OracleParameter(":CELL_CHNAME",OracleType.NVarChar,100)
            };
        _params[0].Value = CellInfo.DataKeys[e.RowIndex].Value;
        int count = OracleHelper.ExecuteNonQuery(sqlstr, CommandType.Text, _params);
        SetPager();
        UnCellPager();
        if (count > 0)
        {
            JQueryAlert("基站删除成功!");
        }
    }

    protected void UnCellPager_PageChanged(object sender, EventArgs e)
    {
        UnCellPager();
    }

    protected void UploadBtn_Click(object sender, EventArgs e)
    {
        if (CellInfoload.HasFile)
        {
            DataTable dt = null;
            try
            {
                using (MemoryStream stream = new MemoryStream(CellInfoload.FileBytes))
                {
                    int num = 0;
                    dt = ExcelUtils.TranslateToTable(stream, "Sheet1");
                    string sqlstr = String.Empty;
                    OracleHelper.ExecuteNonQuery("TRUNCATE TABLE CELL_CLASS", CommandType.Text, null);
                    sqlstr = @"select CELL_CHNAME AS 中文名称,CELL_ENNAME AS 设备名称,CELL_ADDRESS AS 位置信息
                                           ,CELL_STATUS AS 网元工程状态,CELL_CLASS AS 基站等级
                                           ,CELL_NUMBER AS 小区编号,CELL_CLASSIFICATION AS 网络类别
                                      from CELL_CLASS ";
                    num = OracleHelper.MultiInsertData(sqlstr, dt);
                    OracleHelper.ExecuteNonQuery("AB_MODIFY_2GCELL_P", CommandType.StoredProcedure, null);
                    OracleHelper.ExecuteNonQuery("AB_MODIFY_3GCELL_P", CommandType.StoredProcedure, null);
                    SetPager();
                    UnCellPager();
                    Modify3GCell();
                    ModifyINCell();
                    JQueryAlert("导入成功,共导入" + num.ToString() + "个基站信息！");
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger(GetType()).Error("数据上传出现错误！", ex);
                throw ex;
            }
        }
        else
        {
            JQueryAlert("请选择上传文件！");
        }
    }

    private void Modify3GCell()
    {
        string sqlstr = @"update ALARM_TABLE_3G A set (A.cell_chname,A.Cell_Class)=(select B.CELL_CHNAME,B.Cell_Class from cell_class B
                             WHERE REPLACE(A.RNW_OBJECT_NAME,' ','')=B.CELL_ENNAME) where A.cell_chname is null
                             and exists(select 1 from cell_class B
                             WHERE REPLACE(A.RNW_OBJECT_NAME,' ','')=B.CELL_ENNAME)";
        OracleHelper.ExecuteNonQuery(sqlstr, CommandType.Text, null);
    }

    private void ModifyINCell()
    {
        string sqlstr = @"update cell_class T1 set cell_in = '是'
                                 where exists(select 1 from U_TEMP_CELLIN_T T2 
                                 where Replace(T2.cell_chname,'---工程期','') = T1.CELL_CHNAME
                                 or Replace(T1.cell_chname,'---工程期','') = T2.CELL_CHNAME
                                 or T1.cell_chname = T2.cell_chname)";
        OracleHelper.ExecuteNonQuery(sqlstr, CommandType.Text, null);
    }
}
